


ALTER               PROC sp_projected_actual_revenue_report(

@CUSTOMER_INPUT VARCHAR(100),
@PRODUCT_INPUT VARCHAR(100),
@QUARTER VARCHAR(10),
@YEAR_INPUT INT,
@SALES_PERSON_INPUT VARCHAR(100),
@END_USER_INPUT VARCHAR(100),
@defaultPrompt varchar(20),
@BL_DATE_CATEGORY INT )

AS
BEGIN

	DECLARE @RESULTS TABLE( CUSTOMER VARCHAR(100), 
				PRODUCT VARCHAR(100),
				SALES_PERSON VARCHAR(100),
				END_USER VARCHAR(100),
				QUARTERS INT,
				QUARTER_END INT,
				YEAR INT,
				SF_QTY INT,
				BL_QTY INT,
				SHIP_QTY INT,
				SO_QTY_BOOKED INT,
				MISC VARCHAR(15) )
	
	
	DECLARE @CUSTOMER VARCHAR(100)
	DECLARE @PRODUCT VARCHAR(100)
	DECLARE @SALES_PERSON VARCHAR(100)
	DECLARE @END_USER VARCHAR(100)
	DECLARE @MONTH INT
	DECLARE @YEAR INT
	DECLARE @QUARTER_END INT
	DECLARE @QUANTITY INT
	DECLARE @UNIT_PRICE FLOAT
	DECLARE @REQ_DATE VARCHAR(20)
	DECLARE @SCH_DATE VARCHAR(20)
	DECLARE @BL_TYPE VARCHAR(20)
	DECLARE @ROW_COUNT INT
	DECLARE @SHIP_QTY INT
	DECLARE @BL_QTY INT
	DECLARE @FC_QTY INT
	DECLARE @SF_QUANTITY INT
	DECLARE @QTY_BOOKED INT
	DECLARE @MISC VARCHAR(15) -- Added by Gandhi on 20th Sep 2004
	DECLARE @MKTG_PART_NUMBER VARCHAR(100)

	DECLARE @TEMP_COUNT INT
	SET @TEMP_COUNT = 0
	
	DECLARE @FC_CURSOR CURSOR 
	DECLARE @BL_CURSOR CURSOR
	DECLARE @SHIP_CURSOR CURSOR
	DECLARE @BOOKING_CURSOR CURSOR
	
	DECLARE @MONTH_FROM INT
	DECLARE @MONTH_TO INT  	

	IF( @QUARTER = '1')
	BEGIN
		SET @MONTH_FROM = 0
		SET @MONTH_TO = 2	
	END
	
	IF( @QUARTER = '2')
	BEGIN
		SET @MONTH_FROM = 3
		SET @MONTH_TO = 2	
	END	
	
	IF( @QUARTER = '3')
	BEGIN
		SET @MONTH_FROM = 6
		SET @MONTH_TO = 2	
	END

	IF( @QUARTER = '4')
	BEGIN
		SET @MONTH_FROM = 9
		SET @MONTH_TO = 2	
	END

	IF( @QUARTER = 'All')
	BEGIN
		SET @MONTH_FROM = 0
		SET @MONTH_TO = 11	
	END	

	
	-- INSERT FORECAST DETAILS
	SET @FC_CURSOR = CURSOR FOR
	SELECT * FROM fn_ct_mkg_load_projected_actual_forecast_details( @CUSTOMER_INPUT, @PRODUCT_INPUT, @SALES_PERSON_INPUT, @MONTH_FROM, @MONTH_TO, @YEAR_INPUT, @END_USER_INPUT )
	
	OPEN @FC_CURSOR
		FETCH NEXT FROM @FC_CURSOR
		INTO @CUSTOMER, @PRODUCT, @SALES_PERSON, @END_USER, @MONTH, @YEAR, @SF_QUANTITY, @MISC
	
		WHILE @@FETCH_STATUS = 0
		BEGIN	


			IF( @MONTH >= 0 AND @MONTH <= 2)
			BEGIN
				SET @MONTH = 1
				SET @QUARTER_END = 3
			END
			
			IF( @MONTH >= 3 AND @MONTH <= 5)
			BEGIN
				SET @MONTH = 2
				SET @QUARTER_END = 6
			END
	
			IF( @MONTH >= 6 AND @MONTH <= 8)
			BEGIN
				SET @MONTH = 3
				SET @QUARTER_END = 9
			END
		
			IF( @MONTH >= 9 AND @MONTH <= 11)
			BEGIN
				SET @MONTH = 4
				SET @QUARTER_END = 12
			END

						
			IF( ( @QUARTER = CAST ( @MONTH AS VARCHAR(10) ) ) OR ( @QUARTER = 'All' ) )
			BEGIN
				
			
				IF( @END_USER IS NULL )
				BEGIN
					SELECT @ROW_COUNT = COUNT(*) FROM @RESULTS 
					WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON 
				END
				ELSE
				BEGIN
					SELECT @ROW_COUNT = COUNT(*) FROM @RESULTS 
					WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER
				END
		
				IF @ROW_COUNT = 0
				BEGIN
					INSERT INTO @RESULTS ( CUSTOMER, PRODUCT, SALES_PERSON, END_USER, QUARTERS, QUARTER_END, YEAR, SF_QTY, BL_QTY, SHIP_QTY, SO_QTY_BOOKED, MISC )
					VALUES ( @CUSTOMER, @PRODUCT, @SALES_PERSON, @END_USER, @MONTH, @QUARTER_END, @YEAR, @SF_QUANTITY, 0, 0, 0, @MISC)
				
					/*SELECT @MKTG_PART_NUMBER = Product.P_Marketing_part_Number from product,Process_Step  where Product.Part_Number = @PRODUCT and product.ProcessStep_ID in (select processstep_id from Process_Step where PS_Type_Name like 'Finished Goods') and product.processStep_id=Process_Step.ProcessStep_ID  
				
					IF( @MKTG_PART_NUMBER IS NULL )
					BEGIN
						IF( @END_USER IS NULL )						
						BEGIN
							UPDATE @RESULTS SET PRODUCT =  @PRODUCT WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON
						END
						ELSE
						BEGIN
							UPDATE @RESULTS SET PRODUCT =  @PRODUCT WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER
						END
					END
					ELSE
					BEGIN
						IF( @END_USER IS NULL )						
						BEGIN						
							UPDATE @RESULTS SET PRODUCT =  @MKTG_PART_NUMBER WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON
						END
						ELSE
						BEGIN
							UPDATE @RESULTS SET PRODUCT =  @MKTG_PART_NUMBER WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER
						END
					END*/
		
				END
				ELSE
				BEGIN
					IF( @END_USER IS NULL )
					BEGIN			
						SELECT @FC_QTY = ISNULL(SF_QTY, 0) FROM @RESULTS
						WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON 
			
						UPDATE @RESULTS SET SF_QTY = (@FC_QTY + @SF_QUANTITY)
						WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON 
					
					END
					ELSE
					BEGIN
					
						SELECT @FC_QTY = ISNULL(SF_QTY, 0) FROM @RESULTS
						WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER
			
						UPDATE @RESULTS SET SF_QTY = (@FC_QTY + @SF_QUANTITY)
						WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER
					END
				 END	
		
			END			

			FETCH NEXT FROM @FC_CURSOR
			INTO @CUSTOMER, @PRODUCT, @SALES_PERSON, @END_USER, @MONTH, @YEAR, @SF_QUANTITY, @MISC
		END
	CLOSE @FC_CURSOR
	DEALLOCATE @FC_CURSOR

	
	
	-- INSERT BACKLOG DETAILS
	DECLARE @TODAY VARCHAR(20)
	SET @TODAY = CONVERT(VARCHAR, GETDATE(), 101)
	
	DECLARE @BACKLOG_TYPE INT
	SET @BACKLOG_TYPE = 3

	SET @BL_CURSOR = CURSOR FOR
	SELECT * FROM fn_ct_sls_load_backlog( @CUSTOMER_INPUT, @PRODUCT_INPUT, @BL_DATE_CATEGORY, @MONTH_FROM, @MONTH_TO, @YEAR_INPUT, @TODAY, @BACKLOG_TYPE )
	
	OPEN @BL_CURSOR
		FETCH NEXT FROM @BL_CURSOR
		INTO @SALES_PERSON, @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE, @REQ_DATE, @SCH_DATE, @BL_TYPE, @END_USER
	
		WHILE @@FETCH_STATUS = 0
		BEGIN
	
			
			IF( @MONTH >= 0 AND @MONTH <= 2)
			BEGIN
				SET @MONTH = 1
				SET @QUARTER_END = 3
			END
			
			IF( @MONTH >= 3 AND @MONTH <= 5)
			BEGIN
				SET @MONTH = 2
				SET @QUARTER_END = 6
			END
	
			IF( @MONTH >= 6 AND @MONTH <= 8)
			BEGIN
				SET @MONTH = 3
				SET @QUARTER_END = 9
			END
		
			IF( @MONTH >= 9 AND @MONTH <= 11)
			BEGIN
				SET @MONTH = 4
				SET @QUARTER_END = 12
			END

						
			IF( ( @QUARTER = CAST ( @MONTH AS VARCHAR(10) ) ) OR ( @QUARTER = 'All' ) )
			BEGIN
				
				SELECT @ROW_COUNT = COUNT(*) FROM @RESULTS 
				WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER
			
				IF ( @ROW_COUNT <> 0 AND @TEMP_COUNT = 0 )
				BEGIN  
					UPDATE @RESULTS SET BL_QTY =  @QUANTITY
					WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER
				END
				IF ( @ROW_COUNT <> 0 AND @TEMP_COUNT = 1 )
				BEGIN
					SELECT @BL_QTY = ISNULL(BL_QTY, 0) FROM @RESULTS
					WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER
	
					UPDATE @RESULTS SET BL_QTY = (@BL_QTY + @QUANTITY)
					WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER
				END
					SET @TEMP_COUNT = 1
			END
				
			FETCH NEXT FROM @BL_CURSOR
			INTO @SALES_PERSON, @CUSTOMER, @PRODUCT, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE, @REQ_DATE, @SCH_DATE, @BL_TYPE, @END_USER

		END

	CLOSE @BL_CURSOR
	DEALLOCATE @BL_CURSOR


	-- INSERT SHIPPED DETAILS
	SET @SHIP_CURSOR = CURSOR FOR
	SELECT * FROM fn_shipped_orders_by_month_for_fcbl_report( @CUSTOMER_INPUT, @PRODUCT_INPUT, @SALES_PERSON_INPUT, @MONTH_FROM, @MONTH_TO, @YEAR_INPUT )
	
	OPEN @SHIP_CURSOR	
		FETCH NEXT FROM @SHIP_CURSOR
		INTO @CUSTOMER, @PRODUCT, @SALES_PERSON, @END_USER, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE
		
		WHILE @@FETCH_STATUS = 0
		BEGIN


			IF( @MONTH >= 0 AND @MONTH <= 2)
			BEGIN
				SET @MONTH = 1
				SET @QUARTER_END = 3
			END
			
			IF( @MONTH >= 3 AND @MONTH <= 5)
			BEGIN
				SET @MONTH = 2
				SET @QUARTER_END = 6
			END
	
			IF( @MONTH >= 6 AND @MONTH <= 8)
			BEGIN
				SET @MONTH = 3
				SET @QUARTER_END = 9
			END
		
			IF( @MONTH >= 9 AND @MONTH <= 11)
			BEGIN
				SET @MONTH = 4
				SET @QUARTER_END = 12
			END

			
			IF( ( @QUARTER = CAST ( @MONTH AS VARCHAR(10) ) ) OR ( @QUARTER = 'All' ) )
			BEGIN
	
				SELECT @ROW_COUNT = COUNT(*) FROM @RESULTS 
				WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER
	
				IF ( @ROW_COUNT <> 0 AND @TEMP_COUNT = 0 )
				BEGIN  
					UPDATE @RESULTS SET SHIP_QTY =  @QUANTITY
					WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER
				END
				IF ( @ROW_COUNT <> 0 AND @TEMP_COUNT = 1 )
				BEGIN
					SELECT @SHIP_QTY = ISNULL(SHIP_QTY, 0) FROM @RESULTS
					WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER 
					
					UPDATE @RESULTS SET SHIP_QTY = (@SHIP_QTY + @QUANTITY)
					WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER
				END
					SET @TEMP_COUNT = 1
			END

			FETCH NEXT FROM @SHIP_CURSOR
			INTO @CUSTOMER, @PRODUCT, @SALES_PERSON, @END_USER, @MONTH, @YEAR, @QUANTITY, @UNIT_PRICE
		END
	CLOSE @SHIP_CURSOR
	DEALLOCATE @SHIP_CURSOR


	-- INSERT BOOKING BY SALES ORDERS DETAILS
	SET @BOOKING_CURSOR = CURSOR FOR
	SELECT * FROM fn_booking_by_sales_orders_for_projected_actual_report( @CUSTOMER_INPUT, @PRODUCT_INPUT, @MONTH_FROM, @MONTH_TO, @YEAR_INPUT )
	
	OPEN @BOOKING_CURSOR	
		FETCH NEXT FROM @BOOKING_CURSOR
		INTO @CUSTOMER, @PRODUCT, @SALES_PERSON, @END_USER, @MONTH, @YEAR, @QUANTITY

		WHILE @@FETCH_STATUS = 0
		BEGIN


			IF( @MONTH >= 0 AND @MONTH <= 2)
			BEGIN
				SET @MONTH = 1
				SET @QUARTER_END = 3
			END
			
			IF( @MONTH >= 3 AND @MONTH <= 5)
			BEGIN
				SET @MONTH = 2
				SET @QUARTER_END = 6
			END
	
			IF( @MONTH >= 6 AND @MONTH <= 8)
			BEGIN
				SET @MONTH = 3
				SET @QUARTER_END = 9
			END
		
			IF( @MONTH >= 9 AND @MONTH <= 11)
			BEGIN
				SET @MONTH = 4
				SET @QUARTER_END = 12
			END

			
			IF( ( @QUARTER = CAST ( @MONTH AS VARCHAR(10) ) ) OR ( @QUARTER = 'All' ) )
			BEGIN
	
				SELECT @ROW_COUNT = COUNT(*) FROM @RESULTS 
				WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER
	
				IF ( @ROW_COUNT <> 0 AND @TEMP_COUNT = 0 )
				BEGIN  
					UPDATE @RESULTS SET SO_QTY_BOOKED =  @QUANTITY
					WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER
				END
				IF ( @ROW_COUNT <> 0 AND @TEMP_COUNT = 1 )
				BEGIN
					SELECT @QTY_BOOKED = ISNULL(SO_QTY_BOOKED, 0) FROM @RESULTS
					WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER 
					
					UPDATE @RESULTS SET SO_QTY_BOOKED = ( @QTY_BOOKED + @QUANTITY )
					WHERE CUSTOMER = @CUSTOMER AND PRODUCT = @PRODUCT AND YEAR = @YEAR AND QUARTERS = @MONTH AND SALES_PERSON = @SALES_PERSON AND END_USER = @END_USER
				END
					SET @TEMP_COUNT = 1
			END

			FETCH NEXT FROM @BOOKING_CURSOR
			INTO @CUSTOMER, @PRODUCT, @SALES_PERSON, @END_USER, @MONTH, @YEAR, @QUANTITY

		END
	CLOSE @BOOKING_CURSOR
	DEALLOCATE @BOOKING_CURSOR
	

	IF ( @MISC IS NOT NULL)
	BEGIN
		select * from @results
	END
	ELSE
	BEGIN
		if(@SALES_PERSON_INPUT = '0' and @END_USER_INPUT = '0')
		begin
			select * from @results 
		end
		else if(@SALES_PERSON_INPUT = '0' and @END_USER_INPUT <> '0')
		begin
			select * from @results where END_USER = @END_USER_INPUT
		end
		else if(@SALES_PERSON_INPUT <> '0' and @END_USER_INPUT = '0')
		begin
			select * from @results where SALES_PERSON = @SALES_PERSON_INPUT
		end
		else if(@SALES_PERSON_INPUT <> '0' and @END_USER_INPUT <> '0')
		begin
			select * from @results where SALES_PERSON = @SALES_PERSON_INPUT and END_USER = @END_USER_INPUT
		end
		else
		begin
			select * from @results
		end
	END


END

